﻿﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

/// <summary> 
/// OleDbHelper 的摘要说明 
/// </summary> 
public class OleDbHelper
{
    private static string connString;
    public OleDbHelper(string path)
    {
        connString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + path + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
    }
    //根据Select查询sql，返回DataSet 
    public static DataSet GetDataSet(string sql)
    {
        
        OleDbConnection conn = new OleDbConnection(connString);
        OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    //根据select查询sql，返回OleDbDataReader 
    public static OleDbDataReader GetReader(string sql)
    {
        OleDbDataReader dr = null;
        OleDbConnection conn = new OleDbConnection(connString);
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        conn.Open();
        try
        {
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            conn.Close();
        }
        return dr;
    }

    //根据select查询sql,返回一个整数 
    public static int ExecScalar(string sql)
    {
        int ret;
        OleDbConnection conn = new OleDbConnection(connString);
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        conn.Open();
        try
        {
            ret = (int)cmd.ExecuteScalar();
        }
        finally
        {
            conn.Close();
        }
        return ret;
    }

    //运行Update,Insert,Delete等SQL语句 
    public static int ExecSql(string sql)
    {
        int ret;
        OleDbConnection conn = new OleDbConnection(connString);
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        conn.Open();
        try
        {
            ret = cmd.ExecuteNonQuery();
        }
        finally
        {
            conn.Close();
        }
        return ret;
    }

}